/*
 * SvcRepairDAO.java
 *
 * Created on March 22, 2007, 1:45 PM
 *
 */

package edu.byu.isys413.group1E.data;

import edu.byu.isys413.group1E.handlers.*;
import java.sql.*;
import java.util.ArrayList;

/** This class is to read and write SvcRepairBO information to and from the DB.
 *
 * @author Travis Kulbeth
 */
public class SvcRepairDAO extends RevenueSourceDAO{
    /** Contains an instance of SvcRepairDAO*/
    private static SvcRepairDAO instance = null;
    
    /** Creates a new instance of SvcRepairDAO */
    public SvcRepairDAO() {}
    
    /**Retrieves an instance of the SvcRepairDAO object
     *
     * @return a current singleton instance of the SvcRepairDAO class
     */
    public static synchronized SvcRepairDAO getInstance() {
        if (instance == null) {
            instance = new SvcRepairDAO();
        }
        return instance;
    }
    
    ///////////////////////////////////////////////////////////////////////////////
    /////////   CREATE
    /**
     * Creates a new SvcRepairBO object with a newly generated GUID.
     * 
     * @return a newly created SvcRepairBO with unique GUID
     * @throws java.lang.Exception to catch any errors in the creation process.
     */
    public RevenueSourceBO create() throws Exception{
        String id = GUID.generate();
        SvcRepairBO bo = new SvcRepairBO(id);
        bo.setType("repair");
        Cache c = Cache.getInstance();
        c.put(bo.getRevSrcID(), bo);
        return bo;
    }
    
    /**
     * This is the public read statement.  It loads an existing SvcRepair record
     * from the database based on the RevSrcID and returns a SvcRepairBO.
     * 
     * @return a SvcRepairBO containing the information extracted from the requested
     * table and tuple in the DB.
     * @param id contains the unique identifier for the record to be read from the DB.
     * @throws edu.byu.isys413.group1E.handlers.DataException handles any errors from data
     * @throws java.sql.SQLException catches any errors from the SQL execution
     * @throws edu.byu.isys413.group1E.handlers.ConnectionPoolException catches any errors with the connection
     */
    public RevenueSourceBO read(String id) throws SQLException, DataException, ConnectionPoolException {
        // check to see if id in the cache
        Cache c = Cache.getInstance();
        SvcRepairBO rep = (SvcRepairBO)c.get(id);
        if (rep != null){
            return rep;                                                        // if so, return it immediately
        }
        
        //otherwise, get it from the database
        try {
            Connection conn = ConnectionPool.getInstance().get();             // retrieve a database connection from the pool
            rep = (SvcRepairBO)this.read(id, conn);                                              // call read with a connection
            conn.commit();
            ConnectionPool.getInstance().release(conn);                       // release the connection
        }catch (SQLException e) {
            //rollback
            throw new DataException("Could not retrieve record for id = " + id, e);
        }catch (ConnectionPoolException x) {
            //rollback
            throw new ConnectionPoolException("Could not establish connection to database.", x);
        }
        
        // release the connection back to the pool
        return rep;
    }
    
    /**
     * This is the public read statement.  It loads an existing SvcRepair record
     * from the database based on the RevSrcID, using an already existing connection to the DB,
     * and returns a SvcRepairBO.
     * 
     * @return SvcRepairBO
     * @param id contains the given id for the SvcRepair to be read
     * @param conn contains an already open connection to the DB
     * @throws edu.byu.isys413.group1E.handlers.DataException handles any errors from data
     * @throws java.sql.SQLException catches any errors from the SQL execution
     * @throws edu.byu.isys413.group1E.handlers.ConnectionPoolException catches any errors with the connection
     */
    public synchronized RevenueSourceBO read(String id, Connection conn) throws SQLException, DataException, ConnectionPoolException {
        // check to see if id in the cache
        Cache c = Cache.getInstance();
        SvcRepairBO rep = (SvcRepairBO)c.get(id);
        if (rep != null){
            return rep;                                                        // if so, return it immediately
        }
        
        // if not in the cache, put the newly created object in the cache, request the RevSrcDAO to retrieve it's information, then
        //retrieve the specific SvcRepair information here.
        rep = new SvcRepairBO(id);
        c.put(id,rep);
        rep = (SvcRepairBO)super.read(id, conn);
        
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT * FROM Service_Repair WHERE RevenueSourceID = '" + id + "'");
        conn.commit();
        
        //build the object from the result set
        if (rs.next()) {
            if(rs.getDate("DateStarted") != null){
                rep.setStartDate(rs.getDate("DateStarted").toString());
            }
            if(rs.getDate("DateCompleted") != null){
                rep.setCompleteDate(rs.getDate("DateCompleted").toString());
            }
            if(rs.getDate("LastNoticeDate") != null){
                rep.setLastNoticeDate(rs.getDate("LastNoticeDate").toString());
            }
            if(rs.getDate("DatePickedUp") != null){
                rep.setPickUpDate(rs.getDate("DatePickedUp").toString());
            }
            rep.setLaborHrs(rs.getDouble("LaborHours"));
            rep.setContactMethod(rs.getString("ContactMethod"));
            rep.setEmployeeID(rs.getString("EmployeeID"));
            rep.setProductID(rs.getString("ProductID"));
            rep.setTransDate(rs.getString("TransDate"));
            if(rs.getInt("CoveredByWarranty") == 1){ rep.setCoveredByWarranty(true);}
            else{ rep.setCoveredByWarranty(false); }
            rep.setAlreadyInDB(true);
            rep.setIsDirty(false);
            
            //Retrieve the product
            //first retrieve what type of product it is
            String prodType = TopDownProdHandler.getInstance().getProdType(rep.getProductID(), conn);
            if(prodType.equals("physical")){
                //now determine what type of physical product it is (rental or sale)
                String physType = TopDownPhysHandler.getInstance().getPhysType(rep.getProductID(), conn);
                if(physType.equals("forsale")){
                    rep.setProduct(ForSaleDAO.getInstance().read(rep.getProductID(), conn));
                }else{
                    rep.setProduct(ForRentDAO.getInstance().read(rep.getProductID(), conn));
                }
            }else{
                rep.setProduct(ConceptualDAO.getInstance().read(rep.getProductID(), conn));
            }
            // save to the cache
            c.put(rep.getRevSrcID(), rep);
        }else{
            throw new DataException("Object was not found in the database.");
        }
        stmt.close();
        // return the BO
        return rep;
    }
    
    /**
     * This is a public method to retrieve a list of all SvcRepairs in the DB.
     * 
     * @return a list of all SvcRepairs from the DB.
     * @throws edu.byu.isys413.group1E.handlers.DataException handles any errors from data
     * @throws java.sql.SQLException catches any errors from the SQL execution
     * @throws edu.byu.isys413.group1E.handlers.ConnectionPoolException catches any errors with the connection
     */
    public synchronized ArrayList<RevenueSourceBO> readAll() throws DataException, SQLException, ConnectionPoolException{
        ArrayList<RevenueSourceBO> list = new ArrayList<RevenueSourceBO>();
        //now build the query
        String query = "SELECT * FROM Service_Repair";
        
        Connection conn = ConnectionPool.getInstance().get();
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(query);
        conn.commit();
        
        //extract the elements from the result set and build a list of results
        //build the object from the result set
        while (rs.next()) {
            SvcRepairBO rep = new SvcRepairBO(rs.getString("RevenueSourceID"));
            Cache.getInstance().put(rs.getString("RevenueSourceID"), rep);
            rep = (SvcRepairBO)super.read(rs.getString("RevenueSourceID"), conn);
            
            //populate RS specific fields
            if(rs.getDate("DateStarted") != null){
                rep.setStartDate(rs.getDate("DateStarted").toString());
            }
            if(rs.getDate("DateCompleted") != null){
                rep.setCompleteDate(rs.getDate("DateCompleted").toString());
            }
            if(rs.getDate("LastNoticeDate") != null){
                rep.setLastNoticeDate(rs.getDate("LastNoticeDate").toString());
            }
            if(rs.getDate("DatePickedUp") != null){
                rep.setPickUpDate(rs.getDate("DatePickedUp").toString());
            }
            rep.setLaborHrs(rs.getDouble("LaborHours"));
            rep.setContactMethod(rs.getString("ContactMethod"));
            rep.setEmployeeID(rs.getString("EmployeeID"));
            rep.setProductID(rs.getString("ProductID"));
            rep.setTransDate(rs.getString("TransDate"));
            if(rs.getInt("CoveredByWarranty") == 1){ rep.setCoveredByWarranty(true);}
            else{ rep.setCoveredByWarranty(false); }
            rep.setAlreadyInDB(true);
            rep.setIsDirty(false);
            
            // save to the list
            list.add(rep);
        }//while
        
        stmt.close();
        ConnectionPool.getInstance().release(conn);
        return list;
    }
    
    /**
     * This method is really a part of save(bo, conn) in the super class.  It could be
     * embedded directly in it, but I've separated it into it's own method
     * to isolate the SQL udpate statement and make it more readable.  But
     * logically, it's really just a part of save.
     * 
     * This method is called by the save method in the super class after the common 
     * RS data has been saved to the DB.
     * 
     * @param bo contains the SvcRepairBO to be updated to the DB
     * @param conn contains the connection already established with the DB
     * @throws edu.byu.isys413.group1E.handlers.DataException handles any errors from data
     * @throws java.sql.SQLException catches any errors from the SQL execution
     */
    protected void update(RevenueSourceBO bo, Connection conn) throws SQLException, DataException {
        //update the DB
        SvcRepairBO svc = (SvcRepairBO)bo;
        PreparedStatement pstmt = conn.prepareStatement("UPDATE Service_Repair SET DateStarted = ?, " +
                "DateCompleted = ?, LaborHours = ?, EmployeeID = ?, DatePickedUp = ?, LastNoticeDate = ?, " +
                "ProductID = ?, ContactMethod = ?, TransDate = ?, CoveredByWarranty = ? WHERE revenueSourceID = ?");
        
        if(svc.getStartDate() != null){
            pstmt.setDate(1, java.sql.Date.valueOf(svc.getStartDate()));
        }else{ pstmt.setNull(1, java.sql.Types.DATE); }
        if(svc.getCompleteDate() != null){
            pstmt.setDate(2, java.sql.Date.valueOf(svc.getCompleteDate()));
        }else{ pstmt.setNull(2, java.sql.Types.DATE); }
        pstmt.setDouble(3, svc.getLaborHrs());
        pstmt.setString(4, svc.getEmployeeID());
        if(svc.getPickUpDate() != null){
            pstmt.setDate(5, java.sql.Date.valueOf(svc.getPickUpDate()));
        }else{ pstmt.setNull(5, java.sql.Types.DATE); }
        if(svc.getLastNoticeDate() != null){
            pstmt.setDate(6, java.sql.Date.valueOf(svc.getLastNoticeDate()));
        }else{ pstmt.setNull(6, java.sql.Types.DATE); }
        pstmt.setString(7, svc.getProductID());
        pstmt.setString(8, svc.getContactMethod());
        pstmt.setDate(9, java.sql.Date.valueOf(svc.getTransDate()));
        if(svc.isCoveredByWarranty()){ pstmt.setInt(10, 1);}
        else{ pstmt.setInt(10, 0);}
        pstmt.setString(11, svc.getRevSrcID());
        pstmt.executeUpdate();
        pstmt.close();
        svc.setAlreadyInDB(true);
    }// end update
    
    
    /**
     * This method is really a part of save(bo, conn) in the super class.  It could be
     * embedded directly in it, but I've separated it into it's own method
     * to isolate the SQL udpate statement and make it more readable.  But
     * logically, it's really just a part of save.
     * 
     * This method is called by the save method in the super class after the common 
     * RS data has been saved to the DB.
     * 
     * @param bo contains the SvcRepairBO to be inserted into the DB
     * @param conn contains the connection already established with the DB
     * @throws edu.byu.isys413.group1E.handlers.DataException handles any errors from data
     * @throws java.sql.SQLException catches any errors from the SQL execution
     */
    synchronized void insert(RevenueSourceBO bo, Connection conn) throws SQLException, DataException {
        //update the DB
        SvcRepairBO svc = (SvcRepairBO)bo;
        PreparedStatement pstmt = conn.prepareStatement("INSERT INTO Service_Repair (revenueSourceID, DateStarted, DateCompleted, " +
                "LaborHours, EmployeeID, DatePickedUp, LastNoticeDate, ProductID, ContactMethod, TransDate, CoveredByWarranty)" +
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
        pstmt.setString(1, svc.getRevSrcID());
       if(svc.getStartDate() != null){
            pstmt.setDate(2, java.sql.Date.valueOf(svc.getStartDate()));
        }else{ pstmt.setNull(2, java.sql.Types.DATE); }
        if(svc.getCompleteDate() != null){
            pstmt.setDate(3, java.sql.Date.valueOf(svc.getCompleteDate()));
        }else{ pstmt.setNull(3, java.sql.Types.DATE); }
        pstmt.setDouble(4, svc.getLaborHrs());
        pstmt.setString(5, svc.getEmployeeID());
        if(svc.getPickUpDate() != null){
            pstmt.setDate(6, java.sql.Date.valueOf(svc.getPickUpDate()));
        }else{ pstmt.setNull(6, java.sql.Types.DATE); }
        if(svc.getLastNoticeDate() != null){
            pstmt.setDate(7, java.sql.Date.valueOf(svc.getLastNoticeDate()));
        }else{ pstmt.setNull(7, java.sql.Types.DATE); }
        pstmt.setString(8, svc.getProductID());
        pstmt.setString(9, svc.getContactMethod());
        pstmt.setDate(10, java.sql.Date.valueOf(svc.getTransDate()));
        if(svc.isCoveredByWarranty()){ pstmt.setInt(11, 1);}
        else{ pstmt.setInt(11, 0);}
        pstmt.executeUpdate();
        pstmt.close();
        // tell the object that it's now in the db (so we call update next time not insert)
        svc.setAlreadyInDB(true);
    }//end insert
}
